package com.harry.utility.query;

import java.lang.reflect.Array;
import java.lang.reflect.Method;
import java.lang.reflect.ParameterizedType;
import java.util.Arrays;
import java.util.Collection;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.TreeSet;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Stream;

import org.apache.commons.collections4.SetUtils;
import org.apache.commons.lang3.ArrayUtils;
import org.apache.commons.lang3.StringUtils;
import org.hibernate.criterion.Criterion;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.ProjectionList;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.hibernate.criterion.SimpleProjection;
import org.hibernate.sql.JoinType;
import org.hibernate.type.StringType;
import org.hibernate.type.Type;
import org.springframework.beans.BeanUtils;

import com.harry.basis.BasePoEntity;
import com.harry.decoupling.defi.tool.DataExchange;
import com.harry.utility.BeanCtxUtils;
import com.harry.utility.InnerCriteriaHelper;
import com.harry.utility.InnerUtils;

import lombok.NoArgsConstructor;

/**
 * Hibernate - Criteria 的可序列化、反序列化结构
 */
@NoArgsConstructor
public class InnerCriteria {

	// constructors
	public InnerCriteria(InnerCriterion[] criterions, InnerProjection[] projections, InnerOrderBy[] orders) {
		if (criterions != null)
			this.criterions = criterions;
		if (projections != null)
			this.projections = projections;
		if (orders != null)
			this.orders = orders;
	}

	public InnerCriteria(InnerProjection... projections) {
		this(null, projections, null);
	}

	public InnerCriteria(InnerCriterion... criterions) {
		this(criterions, null, null);
	}

	public InnerCriteria(InnerOrderBy... orders) {
		this(new InnerCriterion[0], new InnerProjection[0], orders);
	}

	public InnerCriteria(InnerCriterion[] criterions, InnerProjection[] projections) {
		this(criterions, projections, null);
	}

	public InnerCriteria(InnerCriterion[] criterions, InnerOrderBy[] orders) {
		this(criterions, null, orders);
	}

	public InnerCriteria(InnerProjection[] projections, InnerOrderBy[] orders) {
		this(null, projections, orders);
	}

	public InnerCriteria(String arg0, String arg1, Object... argn) {
		Object[] args = new Object[argn.length + 2];
		args[0] = arg0;
		args[1] = arg1;
		for (int i = 0; i < argn.length; i++) {
			args[i + 2] = argn[i];
		}
		List<InnerCriterion> criterionList = new LinkedList<>();
		List<InnerProjection> projectionList = new LinkedList<>();
		List<InnerOrderBy> orderByList = new LinkedList<>();
		for (int i = 0; i < args.length; i++) {
			if (InnerCriteriaHelper.operSet().contains(args[i]) || "like".equals(args[i])) {
				criterionList.add(new InnerCriterion((String) args[i], (String) args[++i], args[++i]));
			} else if ("in".equals(args[i])) {
				criterionList.add(new InnerCriterion("in", (String) args[++i], args[++i]));
			} else if (StringUtils.equals("asc", (String) args[i]) || StringUtils.equals("desc", (String) args[i])) {
				orderByList.add(new InnerOrderBy((String) args[i], (String) args[++i]));
			} else {
				projectionList.add(new InnerProjection((String) args[i], (String) args[++i]));
			}
		}
		this.criterions = criterionList.toArray(ArrayUtils.toArray());
		this.projections = projectionList.toArray(ArrayUtils.toArray());
		this.orders = orderByList.toArray(ArrayUtils.toArray());
	}

	// public fileds
	public InnerCriterion[] criterions = new InnerCriterion[0];

	public InnerOrderBy[] orders = new InnerOrderBy[0];

	public InnerProjection[] projections;

	// public methond to transform InnerCriteria to DetachedCriteria
	public static DetachedCriteria loadCriteria(InnerCriteria source, DetachedCriteria target) throws Exception {
		loadAlias(source, target);
		loadCriterions(source.criterions, target);
		loadProjections(source.projections, target);
		loadOrders(source.orders, target);
		return target;
	}

	public static Map<String, Object> addUserIdIfNotExit(String property, Map<String, Object> params, Object... value)
			throws Exception {
		InnerCriteria cri = BeanCtxUtils.getBeanByType(DataExchange.class).parse(params.get("criteria"),
				InnerCriteria.class);
		cri = InnerUtils.ifNull(cri, () -> new InnerCriteria());
		if (!hasEqBuyerId(property, cri.criterions)) {
			if (value.length == 1)
				addEqUserId(property, cri, value[0]);
			else
				addEqUserId(property, cri);
			params.put("criteria", cri);
		}
		return params;
	}

	private static void loadAlias(InnerCriteria cri, DetachedCriteria target) throws Exception {
		Set<String> aliasSet = new TreeSet<>();
		// alias in criterion
		for (InnerCriterion crion : cri.criterions)
			loadAliasUnit(crion, aliasSet, target);
		// alias in projection
		InnerProjection[] prjtns = InnerUtils.ifNull(cri.projections, () -> new InnerProjection[0]);
		for (InnerProjection prjtn : prjtns) {
			loadAliasProperty(prjtn.property, aliasSet, target);
			if (prjtn.op != null && prjtn.op.indexOf(COUNT_FILTER_START) > 0
					&& prjtn.op.lastIndexOf(COUNT_FILTER_END) == prjtn.op.length() - 1) {
				String filterStr = prjtn.op.substring(prjtn.op.indexOf(COUNT_FILTER_START) + 1,
						prjtn.op.lastIndexOf(COUNT_FILTER_END));
				Matcher matcher = PPTY_PTN.matcher(filterStr);
				while (matcher.find())
					loadAliasProperty(matcher.group(), aliasSet, target);
			}
		}
		// alias in order
		for (InnerOrderBy odb : InnerUtils.ifNull(cri.orders, () -> new InnerOrderBy[0])) {
			loadAliasProperty(odb.property, aliasSet, target);
		}
		aliasSet.stream().forEach(p -> target.createAlias(underSc_prnt_prop(p), underSc_prnt(p), JoinType.LEFT_OUTER_JOIN));
	}

	private static void loadCriterions(InnerCriterion[] criterions, DetachedCriteria target) throws Exception {
		Stream.of(loadCriterions(criterions)).forEach(c -> target.add(c));
	}

	private static void loadProjections(InnerProjection[] projections, DetachedCriteria target) throws Exception {
		if (projections == null || projections.length == 0)
			return;
		ProjectionList pl = Projections.projectionList();
		Map<String, String> tableAliasMap = InnerCriteriaHelper.getTableAliasFromDC(target);
		for (int index = 0; index < projections.length; index++) {
			InnerProjection p = projections[index];
			String prop = underSc_prnt_prop(p.property);
			if (null == p.op)
				pl.add(Projections.property(prop), underSc_prnt(p.property).substring(1));
			else if ("max".equals(p.op))
				pl.add(Projections.max(prop).as(p.op + underSc_prnt(prop)));
			else if ("min".equals(p.op))
				pl.add(Projections.min(prop).as(p.op + underSc_prnt(prop)));
			else if ("avg".equals(p.op))
				pl.add(Projections.avg(prop).as(p.op + underSc_prnt(prop)));
			else if ("sum".equals(p.op))
				pl.add(Projections.sum(prop).as(p.op + underSc_prnt(prop)));
			else if ("count".equals(p.op))
				pl.add(Projections.countDistinct(prop).as(p.op + underSc_prnt(prop)));
			else if ("group".equals(p.op))
				pl.add(Projections.groupProperty(prop).as(p.op + underSc_prnt(prop)));
			// custom AGGRE
			else if (p.op.indexOf(COUNT_FILTER_START) < 0 || p.op.lastIndexOf(COUNT_FILTER_END) != p.op.length() - 1)
				continue;
			else if (p.op.indexOf(OP_RATE) == 0)
				rateAggre(isNum(target, p.property) ? "sum" : "count", p, index, tableAliasMap, prop, pl);
			else
				commonAggre(p, index, tableAliasMap, prop, pl);
		}
		target.setProjection(pl);
	}

	private static void loadOrders(InnerOrderBy[] ords, DetachedCriteria target) {
		for (InnerOrderBy ord : InnerUtils.ifNull(ords, () -> new InnerOrderBy[0])) {
			if (ord.property == null) {
				continue;
			} else if ("asc".equalsIgnoreCase(ord.sort)) {
				target.addOrder(Order.asc(underSc_prnt_prop(ord.property)));
			} else if ("desc".equalsIgnoreCase(ord.sort)) {
				target.addOrder(Order.desc(underSc_prnt_prop(ord.property)));
			}
		}
	}

	private static void commonAggre(InnerProjection p, int index, Map<String, String> tableAliasMap, String prop,
			ProjectionList pl) {
		String[] sqlAlias = aggreSql(p, index, tableAliasMap, prop);
		pl.add(Projections.sqlProjection(sqlAlias[0] + sqlAlias[1], new String[] { sqlAlias[1] },
				new Type[] { StringType.INSTANCE }));
	}

	private static void rateAggre(String aggre, InnerProjection p, int index, Map<String, String> tableAliasMap,
			String prop, ProjectionList pl) {
		InnerProjection projection = new InnerProjection(p.op.replace(OP_RATE, aggre), p.property);
		// 1. query the sum or count of property with filter
		String[] sqlAlias = aggreSql(projection, index, tableAliasMap, prop);
		sqlAlias[1] = OP_RATE + "_some_" + index + underSc_prnt(prop);
		pl.add(Projections.sqlProjection(sqlAlias[0] + sqlAlias[1], new String[] { sqlAlias[1] },
				new Type[] { StringType.INSTANCE }));
		// 2. query the sum or count of property without filter
		SimpleProjection hiberProjection = aggre.equals("count") ? Projections.countDistinct(prop) : Projections.sum(prop);
		pl.add(hiberProjection.as(OP_RATE + "_all_" + index + underSc_prnt(prop)));
	}

	private static boolean isNum(DetachedCriteria target, String property) throws Exception {
		String[] propSplits = property.split("\\.");
		Class<?> clazz = Class.forName(InnerCriteriaHelper.getImplFromDC(target).getEntityOrClassName());
		for (int i = 0; i < propSplits.length; i++) {
			String prop = propSplits[i];
			Method method = null;
			try {
				method = BeanUtils.getPropertyDescriptor(clazz, prop).getReadMethod();
				clazz = method.getReturnType();
			} catch (Exception e) {
				throw new Error(clazz.getSimpleName() + " has no attribute[" + prop + "]");
			}
			if (Collection.class.isAssignableFrom(clazz)) {
				clazz = (Class<?>) ((ParameterizedType) method.getGenericReturnType()).getActualTypeArguments()[0];
			}
		}
		return Number.class.isAssignableFrom(clazz) || (clazz.getSuperclass() == null && clazz != Object.class);
	}

	private static String[] aggreSql(InnerProjection p, int index, Map<String, String> tableAliasMap, String prop) {
		String aggre = p.op.substring(0, p.op.indexOf(COUNT_FILTER_START));
		String filterStr = p.op.substring(p.op.indexOf(COUNT_FILTER_START) + 1, p.op.lastIndexOf(COUNT_FILTER_END));
		String sqlAlias = aggre + "_" + index + underSc_prnt(prop);
		String sqlTemplate = "%s(%s case %s when true then %s else %s end %s ) as ";
		String distinct = aggre.equals("count") || aggre.equals("group_concat") ? "distinct" : "";
		String filterExpression = hiberFilterString(filterStr, tableAliasMap);
		String columnExpression = hiberColumnString(aggre, p.property, tableAliasMap);
		String emptyExpression = hiberEmptyExpression(aggre, p.property, tableAliasMap);
		String paramExpression = hiberParamExpression(aggre, p.property, tableAliasMap);
		String sql = String.format(sqlTemplate, aggre, distinct, filterExpression, columnExpression, emptyExpression,
				paramExpression);
		return new String[] { sql, sqlAlias };
	}

	private static String hiberParamExpression(String aggre, String property, Map<String, String> tableAliasMap) {
		String tableAlias = property.indexOf(".") < 0 ? "{alias}" : tableAliasMap.get(calcTableNameFromProperty(property));
		return aggre.equals("group_concat") ? String.format("ORDER BY %s.create_time ASC SEPARATOR '_$split$_'", tableAlias)
				: "";
	}

	private static String hiberEmptyExpression(String aggre, String property, Map<String, String> tableAliasMap) {
		return "null";
	}

	private static String hiberColumnString(String aggre, String property, Map<String, String> tableAliasMap) {
		String tableAlias = property.indexOf(".") < 0 ? "{alias}" : tableAliasMap.get(calcTableNameFromProperty(property));
		String columnAlias = property.indexOf(".") < 0 ? InnerUtils.toUnderScoreCase(property)
				: InnerUtils.toUnderScoreCase(calcColumnNameFromProperty(property));
		String columnExp = aggre.equals("group_concat")
				? String.format("concat(%s.id,'$$$',ifnull(%s.%s,'null'))", tableAlias, tableAlias, columnAlias)
				: String.format("%s.%s", tableAlias, columnAlias);
		return columnExp;
	}

	private static String calcTableNameFromProperty(String property) {
		return underSc_prnt(property.substring(0, property.lastIndexOf(".")));
	}

	private static String calcColumnNameFromProperty(String property) {
		return property.substring(property.lastIndexOf(".") + 1, property.length());
	}

	private static String hiberFilterString(String filterStr, Map<String, String> tableAliasMap) {
		Matcher matcher = PPTY_PTN.matcher(filterStr);
		String fieldPath = filterStr;
		for (int workedLength = 0; matcher.find();) {
			String piece = matcher.group();
			int workStartIndex = workedLength + fieldPath.substring(workedLength).indexOf(piece);
			int workEndIndex = workStartIndex + piece.length() - 1;
			int workLeftLength = fieldPath.length() - (workEndIndex + 1);
			fieldPath = matcherPatch(fieldPath, piece, tableAliasMap, workedLength);
			workedLength = fieldPath.length() - workLeftLength;
		}
		return fieldPath;
	}

	private static String matcherPatch(String filterStr, String piece, Map<String, String> tableAliasMap,
			int workedLength) {
		int lastIndex = piece.lastIndexOf(".");
		if (isKeyWord(piece)) {
			return replacePieceLeft(filterStr, piece, piece, workedLength);
		} else if (lastIndex > 0) {
			String columnName = piece.substring(lastIndex + 1, piece.length());
			String tableName = piece.substring(0, lastIndex);
			String realTableName = underSc_prnt(tableName);
			filterStr = replacePieceLeft(filterStr, tableName + "." + columnName,
					tableAliasMap.get(realTableName) + "." + InnerUtils.toUnderScoreCase(columnName), workedLength);
			return filterStr;
		} else {
			return replacePieceLeft(filterStr, piece, "{alias}." + InnerUtils.toUnderScoreCase(piece), workedLength);
		}
	}

	private static String replacePieceLeft(String src, String piece, String replacing, int workedLength) {
		String workedPiece = src.substring(0, workedLength);
		src = src.substring(workedLength);
		int start = src.indexOf(piece);
		return workedPiece + src.substring(0, start) + replacing + src.substring(start + piece.length());
	}

	private static void addEqUserId(String property, InnerCriteria cri, Object... value) {
		List<InnerCriterion> list = new LinkedList<InnerCriterion>();
		list.addAll(Arrays.asList(cri.criterions));
		InnerCriterion crion = new InnerCriterion();
		crion.op = "eq";
		crion.property = property;
		if (value.length == 1)
			crion.setValue(value[0]);
		else
			crion.setValue(InnerUtils.getUser().getId());
		list.add(crion);
		cri.criterions = list.toArray(new InnerCriterion[list.size()]);
	}

	private static boolean hasEqBuyerId(String property, InnerCriterion[] crions) {
		return Arrays.asList(crions).stream().filter(crion -> "eq".equals(crion.op) && property.equals(crion.property))
				.count() != 0;
	}

	private static void loadAliasProperty(String property, Set<String> aliasSet, DetachedCriteria target)
			throws Exception {
		if (property == null)
			return;
		else if (hasAlreadyAlias(property))
			return;
		else if (isKeyWord(property))
			return;
		else {
			String[] propSplits = property.split("\\.");
			Class<?> clazz = Class.forName(InnerCriteriaHelper.getImplFromDC(target).getEntityOrClassName());
			for (int i = 0; i < propSplits.length; i++) {
				String prop = propSplits[i];
				Method method = null;
				try {
					method = BeanUtils.getPropertyDescriptor(clazz, prop).getReadMethod();
					clazz = method.getReturnType();
				} catch (Exception e) {
					throw new Error(clazz.getSimpleName() + " has not attr[" + prop + "]");
				}
				if (Collection.class.isAssignableFrom(clazz)) {
					clazz = (Class<?>) ((ParameterizedType) method.getGenericReturnType()).getActualTypeArguments()[0];
				}
			}
			// alias the parent if it's not Association
			if (!isEntityClass(clazz))
				propSplits = Arrays.copyOf(propSplits, propSplits.length - 1);
			setAliasFromPaths(aliasSet, propSplits);
		}
	}

	private static void setAliasFromPaths(Set<String> aliasSet, String[] paths) {
		String propertyPre = "";
		for (int i = 0; i < paths.length; i++)
			aliasSet.add((propertyPre = propertyPre + "." + paths[i]).substring(1));
	}

	private static boolean isEntityClass(Class<?> clazz) {
		return BasePoEntity.class.isAssignableFrom(clazz);
	}

	private static boolean hasAlreadyAlias(String property) {
		return property.indexOf("_") >= 0;
	}

	private static void loadAliasUnit(InnerCriterion crion, Set<String> aliasSet, DetachedCriteria target)
			throws Exception {
		loadAliasProperty(crion.property, aliasSet, target);
		Object value = crion.getValue();
		if (isPropertyValue(value))
			loadAliasProperty(value2property((String) value), aliasSet, target);
		if (crion.criterions != null)
			for (int i = 0; i < crion.criterions.length; i++)
				loadAliasUnit(crion.criterions[i], aliasSet, target);
	}

	private static boolean isPropertyValue(Object value) {
		return String.class.isInstance(value) && Pattern.matches(String.format("^(\\$\\{)%s\\}$", COLUMN), (String) value);
	}

	private static Criterion[] loadCriterions(InnerCriterion[] crions) throws Exception {
		crions = InnerUtils.ifNull(crions, () -> new InnerCriterion[0]);
		LinkedList<Criterion> crionList = new LinkedList<>();
		for (InnerCriterion cri : crions) {
			// 1. IS NULL
			if ("eq".equals(cri.op) && cri.getValue() == null)
				crionList.add(Restrictions.isNull(underSc_prnt_prop(cri.property)));
			else if ("ne".equals(cri.op) && cri.getValue() == null)
				crionList.add(Restrictions.isNotNull(underSc_prnt_prop(cri.property)));
			// 2. Like
			else if ("like".equals(cri.op))
				crionList.add(Restrictions.like(underSc_prnt_prop(cri.property), cri.getValue()));
			// 3. Simple Criterion eq ne lt le gt ge
			else if (InnerCriteriaHelper.operSet().contains(cri.op))
				addSimpleCriterion(crionList, cri);
			// 4. Composite Criterion
			else if ("or".equals(cri.op))
				crionList.add(Restrictions.or(loadCriterions(cri.criterions)));
			else if ("and".equals(cri.op))
				crionList.add(Restrictions.and(loadCriterions(cri.criterions)));
			else if ("not".equals(cri.op))
				crionList.add(Restrictions.not(Restrictions.and(loadCriterions(cri.criterions))));
			// isn't "in", skip and go on.
			else if (!"in".equals(cri.op))
				continue;
			// is "in"
			else if (isNullOrEmpty(cri.getValue()))
				crionList.addFirst(Restrictions.sqlRestriction("1!=1"));
			else if (isCollection(cri.getValue()))
				crionList.add(Restrictions.in(underSc_prnt_prop(cri.property), ((Collection<?>) cri.getValue()).toArray()));
			else if (isArray(cri.getValue()))
				crionList.add(Restrictions.in(underSc_prnt_prop(cri.property), toObjectArray(cri.getValue())));
			else
				crionList.add(Restrictions.eq(underSc_prnt_prop(cri.property), cri.getValue()));
		}
		return crionList.toArray(new Criterion[crionList.size()]);
	}

	private static void addSimpleCriterion(LinkedList<Criterion> crionList, InnerCriterion cri) throws Exception {
		Object value = cri.getValue();
		if (isPropertyValue(value)) {
			crionList
					.add((Criterion) Restrictions.class.getMethod(String.format("%sProperty", cri.op), String.class, String.class)
							.invoke(null, underSc_prnt_prop(cri.property), underSc_prnt_prop(value2property((String) value))));
		} else {
			crionList.add((Criterion) Restrictions.class.getMethod(cri.op, String.class, Object.class).invoke(null,
					underSc_prnt_prop(cri.property), value));
		}
	}

	private static String value2property(String value) {
		return value.substring(2, value.length() - 1);
	}

	private static boolean isArray(Object value) {
		return value.getClass().isArray();
	}

	private static boolean isCollection(Object value) {
		return Collection.class.isInstance(value);
	}

	private static Object[] toObjectArray(Object value) {
		Object[] values = new Object[Array.getLength(value)];
		for (int i = 0; i < values.length; i++)
			values[i] = Array.get(value, i);
		return values;
	}

	private static boolean isNullOrEmpty(Object value) {
		return value == null || (Collection.class.isInstance(value) && ((Collection<?>) value).size() == 0)
				|| (value.getClass().isArray() && Array.getLength(value) == 0);
	}

	private static String underSc_prnt_prop(String property) {
		if (property.indexOf(".") < 0)
			return property;
		else
			return underSc_prnt(property.substring(0, property.lastIndexOf(".")))
					+ property.substring(property.lastIndexOf("."), property.length());
	}

	private static String underSc_prnt(String parent) {
		return ("_" + parent.replaceAll("\\.", "_")).replaceAll("__", "_");
	}

	private static boolean isKeyWord(String property) {
		return KEY_WORDS.contains(property.toLowerCase());
	}

	private static final String COUNT_FILTER_START = "(";

	private static final String COUNT_FILTER_END = ")";

	private static final String COLUMN_UNIT = "[a-z][A-Za-z0-9]+";

	private static final String COLUMN = String.format("%s(\\.%s)*", COLUMN_UNIT, COLUMN_UNIT);

	private static final Pattern PPTY_PTN = Pattern.compile(COLUMN);

	private static final Set<String> KEY_WORDS = SetUtils.unmodifiableSet("in", "or", "and", "not", "like");

	public static final String OP_RATE = "rate";
}
